package web1.orm;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import web1.bean.user;

public class DBhelp {
private Connection getConn() {
	String driver="com.mysql.jdbc.Driver";
	String url="jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf-8&useSSL=false";
	String username="root";
	String password="Root123@";
	Connection conn=null;
	try {
		Class.forName(driver);
		conn=(Connection)DriverManager.getConnection(url,username,password);
	}catch(Exception e){
		e.printStackTrace();
	}
	return conn;
}
public int insert(user u) {
	Connection conn=getConn();
	int i=0;
	String sql="insert into user(Name,Age) values(?,?)";
	PreparedStatement pstmt;
	try {
		pstmt=(PreparedStatement)conn.prepareStatement(sql);
		pstmt.setString(1, u.getName());
		pstmt.setInt(2, u.getAge());
		i=pstmt.executeUpdate();
		System.out.println("insert: "+i);
		pstmt.close();
		conn.close();
	}catch(SQLException e){
		e.printStackTrace();
	}
	return i;	
}
public int delete(String name) {
	Connection conn=getConn();
	int i=0;
	String sql="delete from user where Name='"+name+"'";
	PreparedStatement pstmt;
	try {
		pstmt=(PreparedStatement)conn.prepareStatement(sql);
		i=pstmt.executeUpdate();
		System.out.println("delete: "+i);
		pstmt.close();
		conn.close();
	}catch(SQLException e){
		e.printStackTrace();
	}
	return i;	
}
public int update(user t) {
	Connection conn=getConn();
	int i=0;
	String sql="update user set Age="+t.getAge()+" where Name='"+t.getName()+"'";
	PreparedStatement pstmt;
	try {
		pstmt=(PreparedStatement)conn.prepareStatement(sql);
		i=pstmt.executeUpdate();
		System.out.println("update: "+i);
		pstmt.close();
		conn.close();
	}catch(SQLException e){
		e.printStackTrace();
	}
	return i;	
}
public user getOne(int id) {
		Connection conn=getConn();
		int i=0;
		String sql="select id,name,age from user where id=?";
		PreparedStatement pstmt;
		try {
			pstmt=(PreparedStatement)conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			ResultSet rs=pstmt.executeQuery();
			user u=new user();
			if(rs.next()) {
				u.setId(rs.getInt(1));
				u.setName(rs.getString(2));
				u.setAge(rs.getInt(3));
			}
			System.out.println("==========getOne===begin===============");
            System.out.println("======u.id=="+u.getId()+"===========");
            System.out.println("======u.name=="+u.getName()+"===========");
            System.out.println("======u.age=="+u.getAge()+"===========");
            System.out.println("==========getOne===end===============");
		}catch(SQLException e){
			e.printStackTrace();
		}

	return null;
	
}
public Integer getAll() {
	Connection conn=getConn();
	String sql="select * from user ";
	PreparedStatement pstmt;
	try {
		pstmt=(PreparedStatement)conn.prepareStatement(sql);
		ResultSet rs=pstmt.executeQuery();
		int col=rs.getMetaData().getColumnCount();
		System.out.println("==========getAll===begin===============");
        System.out.println("name,	age,	group,	id");
       while(rs.next()) {
    	   for(int i=1;i<=col;i++) {
    		   System.out.println(rs.getString(i)+"\t");
    		   if((i==2)&&(rs.getString(i).length()<8)) {
    			   System.out.println("\t");
    		   }
    	   }
    	   System.out.println("");
       }
       System.out.println("============getAll end================");
	}catch(SQLException e){
		e.printStackTrace();
	}

	return null;
	
}


}
